Settings and Keywords
Keyword WITH
Use WITH keyword to declare aliases, variables, expressions. You can use the declared elements in the rest of the query.
WITH
entries AS array(L2EntryNew) AS 'l2',
max(l2[side == BID].price) AS 'maxBid',
min(l2[side == ASK].price) AS 'minAsk'
SELECT
max{}(maxBid) AS 'highBid',
min{}(minAsk) AS 'lowAsk'
FROM binance
OVER time(1m)
WHERE packageType == PERIODICAL_SNAPSHOT AND symbol == 'BTC/USDT'
Keyword TYPE
Use keyword TYPE to set the type name of the query result. Can be used to map the query output to the specific class type.
SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "deltix.timebase.api.messages.BestBidOfferMessage"
FROM binance
ARRAY JOIN (entries AS array(L1Entry))[THIS IS NOT NULL] AS bbo
Keyword FIELD
You can use the FIELD keyword to assign names to columns in the SELECT expression.
tip
It is important to differentiate column names that can be set using FIELD keyword from aliases set by AS keyword. You cannot have more than one alias with the same name, but with FIELD, you can assign the same names to more than one column - refer to the below example.
Refer to Alias to learn more.
SELECT
RECORD
entry.price FIELD 'price'
TYPE t1 WHEN entry is L1Entry
RECORD
entry.price FIELD 'price',
entry.size FIELD 'size'
TYPE t2 WHEN entry is L2EntryNew
FROM binance
ARRAY JOIN entries AS entry
Another useful example of the FIELD keyword usage is when you need to override the output field names that coincide with the input field names. This helps avoid naming conflicts and improves the clarity of your query results.
In the example below, we use entries and packageType as input parameters for orderbook. However, we also need to use entries and packageType as output field names:
WITH
orderbook{maxDepth: 20}(packageType, entries) AS book
SELECT
book FIELD entries,
PERIODICAL_SNAPSHOT FIELD packageType
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM "BINANCE"
OVER TIME (1m)
WHERE symbol == 'BTC/USDT'
Keyword THIS
Use keyword THIS to reference the current message as object.
-- returns the entire message as object as a single field
SELECT THIS FROM binance
-- returns entries array from the current message
SELECT THIS.entries FROM binance
Case and Special Characters
Identifiers in QQL are not case sensitive and get converted to upper case in case not embraced in double quotes. The following rule applies:
- test = Test = TEST
- "test" != "Test" != "TEST"
You do not have to embrace identifiers in quotes, but mind the following use cases:
- use double quotes with identifier's name in case it includes special characters
- use double quotes with identifier's name in case it starts with a numerical
QQL processor performs case-insensitive matching of fields, classes, streams.
QQL supports backslash quotation, such as \', \", \\, \n, \r, \t, \b, \f.
Example: s4'2 in QQL query write as 's4\'2'
Keywords LIMIT and OFFSET
LIMIT keyword sets the number of records returned by the query. For example, adding limit 10 to the query will return just 10 records. OFFSET keyword applies to LIMIT and sets the starting record number. For example, offset 100 added to the query will return all records starting from the 101th record (when counting from 1).
Follow these rules to add LIMIT and OFFSET to QQL queries:
limit <limit count>limit <limit count> offset <offset count>limit <offset count>, <limit count>
As a use case example, a combination of LIMIT and OFFSET can be applied to creating pagination.
SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10 OFFSET 5
SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10, 15
tip
Please note, that a query with the OFFSET will still read all the records on the server side and return just the records considering the specified offset.